using System;
using System.Data;
using System.Data.OleDb;
using System.IO;
using MySql.Data.MySqlClient;
using LdExpress;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Configuration;
using System.Collections;

namespace LdExpress
{
    public class ContentItem
    {
        public string FileFullPath = "";
        public string FileFullPath1 = "";
        public string ShortFileName = "";
        public string NameImage1 = "";
        public long ID = -1;
        public string Name = "";
        public string Summary = "";
        public string Detail = "";
        public DateTime CreatedDate = DateTime.Now;
        public long CreatedByID = -1;
        public DateTime LastUpdatedDate = DateTime.Now;
        public long LastUpdatedByID = -1;
        public DateTime StartDate = DateTime.Now;
        public DateTime EndDate = DateTime.Now;
        public int Degree = 0;
        public string Image1 = "";
        public string Image2 = "";

        public DateTime PostedDate = DateTime.Now;
        public long PostedByID = -1;

        public int IsPosted = 0;
        public int IsVisible = 1;
        public int IsCompleted = 0;
        public int IsNeverExpired = 1;

        public long OwnerID = -1;
        public long SourceID = -1;
        public string SourceName = "";


        #region Constants: ContentItem Statuses
        public const int New = -2;		// IsCompleted = No *
        public const int Completed = -1; // IsCompleted = Yes
        public const int UnPosted = 0;	// IsCompleted = Yes && IsPosted = No *
        public const int Posted = 1;	// IsCompleted = Yes && IsPosted = Yes		
        public const int Pending = 2;	// IsCompleted = Yes && IsPosted = Yes && StartDate > Now *
        public const int Active = 3;	// IsCompleted = Yes && IsPosted = Yes
        // && StartDate <= Now and ( (EndDate > Now) or (IsNeverExpired == Yes) )
        public const int Expired = 4;	// IsCompleted = Yes && IsPosted = Yes *
        // && (EndDate < Now) && (IsNeverExpired == No)
        public const int InVisible = 5; // IsVisible = No
        public const int All = 6;		// No filter
        #endregion

        public ContentItem()
        {
        }
        public ContentItem(long lID)
        {
            ID = lID;
        }

        #region Basic methods
        public void Save(MySqlConnection cn, MySqlTransaction trans)
        {
            string strSQL = "";
            bool bNew = false;
            if (ID <= 0) // new object, we insert new record to database
            {
                //ID = DBModule.GetNewID(typeof(ContentItem), "tbl_contentItem", cn, trans);
                bNew = true;

                // build SQL statement
                strSQL = "Insert into tbl_contentItem (Name,Summary,Detail," +
                    "CreatedDate,CreatedByID,PostedByID,LastUpdatedDate,LastUpdatedByID," +
                    "StartDate,EndDate,Degree," +
                    "IsPosted,IsVisible,IsCompleted,IsNeverExpired,Image1,Image2,OwnerID,SourceID,SourceName) " +
                    "values ('" + DBModule.RefineString(Name) + "','" +
                    DBModule.RefineString(Summary) + "','" + DBModule.RefineString(Detail) + "'," +
                    DBModule.RefineDatetime(CreatedDate) + "," + CreatedByID + "," + PostedByID + "," +
                    DBModule.RefineDatetime(LastUpdatedDate) + "," + LastUpdatedByID + "," +
                    DBModule.RefineDatetime(StartDate) + "," + DBModule.RefineDatetime(EndDate) + "," + Degree.ToString() + "," +
                    IsPosted.ToString() + "," + IsVisible.ToString() + "," +
                    IsCompleted.ToString() + "," +
                    IsNeverExpired.ToString() + ",'" +
                    DBModule.RefineString(Image1) + "','" + DBModule.RefineString(Image2) + "'," + OwnerID.ToString() + "," +
                    SourceID.ToString() + ",'" + DBModule.RefineString(SourceName) + "')";
                UserObjectsLog.SaveObjectActionLog("ContentItem", "add", cn, trans);
            }
            else // edit object, we update old record in database
            {
                // build SQL statement
                strSQL = "Update tbl_contentItem set Name = '" + DBModule.RefineString(Name) + "'," +
                    "Summary='" + DBModule.RefineString(Summary) + "'," +
                    "Detail='" + DBModule.RefineString(Detail) + "'," +
                    "CreatedDate=" + DBModule.RefineDatetime(CreatedDate) + "," +
                    "CreatedByID=" + CreatedByID + "," +
                    "PostedByID=" + PostedByID + "," +
                    "LastUpdatedDate=" + DBModule.RefineDatetime(LastUpdatedDate) + "," +
                    "LastUpdatedByID=" + LastUpdatedByID + "," +
                    "StartDate=" + DBModule.RefineDatetime(StartDate) + "," +
                    "EndDate=" + DBModule.RefineDatetime(EndDate) + "," +
                    "Degree=" + Degree.ToString() + "," +
                    "IsPosted=" + IsPosted.ToString() + "," +
                    "IsVisible=" + IsVisible.ToString() + "," +
                    "IsCompleted=" + IsCompleted.ToString() + "," +
                    "IsNeverExpired=" + IsNeverExpired.ToString() + ", " +
                    "Image1='" + DBModule.RefineString(Image1) + "'," +
                    "Image2='" + DBModule.RefineString(Image2) + "', " +
                    "OwnerID=" + OwnerID.ToString() + ", " +
                    "SourceID=" + SourceID.ToString() + ", " +
                    "SourceName='" + DBModule.RefineString(SourceName) + "' " +
                    "where ID=" + ID.ToString();
                UserObjectsLog.SaveObjectActionLog("ContentItem", "edit", cn, trans);
            }
            // run SQL statement
            DBModule.ExecuteNonQuery(strSQL, cn, trans);

            //if (bNew)
            //{
            //    strSQL = "Insert into TG_VisitNumber(ID, NumOfVisits) values(" + ID +
            //        ", 0)";

            //    DBModule.ExecuteNonQuery(strSQL, cn, trans);
            //}
        }
        public void SaveandUpload(System.Web.HttpPostedFile inpFileImage1, System.Web.HttpPostedFile inpFileImage2, MySqlConnection cn, MySqlTransaction trans)
        {
            string strSQL = "";
            bool bNew = false;
            if (ID <= 0) // new object, we insert new record to database
            {
                //ID = DBModule.GetNewID(typeof(ContentItem), "tbl_contentItem", cn, trans);
                bNew = true;

                Document.CheckDirectory(Document.FileDirPhysicalPathContentItem + ID.ToString());
                if (inpFileImage1 != null)
                {
                    if (inpFileImage1.FileName.Length > 0)
                    {
                        NameImage1 = Document.GetShortFileName(inpFileImage1.FileName);
                        Image1 = "ContentItem" + "\\" + ID.ToString() + "\\" + NameImage1;
                        FileFullPath = Document.FileDirPhysicalPathContentItem + ID.ToString() + "\\" + NameImage1;
                    }
                }
                else
                {
                    Image1 = "ContentItem" + "\\" + ID.ToString() + "\\" + NameImage1;
                    FileFullPath = Document.FileDirPhysicalPathContentItem + ID.ToString() + "\\" + NameImage1;
                }
                if (inpFileImage2 != null)
                {
                    if (inpFileImage2.FileName.Length > 0)
                    {
                        ShortFileName = Document.GetShortFileName(inpFileImage2.FileName);
                        Image2 = "ContentItem" + "\\" + ID.ToString() + "\\" + ShortFileName;
                        FileFullPath1 = Document.FileDirPhysicalPathContentItem + ID.ToString() + "\\" + ShortFileName;
                    }
                }
                else
                {
                    Image2 = "ContentItem" + "\\" + ID.ToString() + "\\" + ShortFileName;
                    FileFullPath1 = Document.FileDirPhysicalPathContentItem + ID.ToString() + "\\" + ShortFileName;
                }
                // build SQL statement
                strSQL = "Insert into tbl_contentItem (ID,Name,Summary,Detail," +
                    "CreatedDate,CreatedByID,LastUpdatedDate,LastUpdatedByID," +
                    "StartDate,EndDate,Degree," +
                    "IsPosted,IsVisible,IsCompleted,IsNeverExpired,Image1,Image2,OwnerID,SourceID,SourceName) " +
                    "values (" + ID + ",'" + DBModule.RefineString(Name) + "','" +
                    DBModule.RefineString(Summary) + "','" + DBModule.RefineString(Detail) + "'," +
                    DBModule.RefineDatetime(CreatedDate) + "," + CreatedByID + "," +
                    DBModule.RefineDatetime(LastUpdatedDate) + "," + LastUpdatedByID + "," +
                    DBModule.RefineDatetime(StartDate) + "," + DBModule.RefineDatetime(EndDate) + "," + Degree.ToString() + "," +
                    IsPosted.ToString() + "," + IsVisible.ToString() + "," +
                    IsCompleted.ToString() + "," +
                    IsNeverExpired.ToString() + ",'" +
                    DBModule.RefineString(Image1) + "','" + DBModule.RefineString(Image2) + "'," + OwnerID.ToString() + "," +
                    SourceID.ToString() + ",'" + DBModule.RefineString(SourceName) + "')";
            }
            else // edit object, we update old record in database
            {
                if (inpFileImage1 != null)
                {
                    if (inpFileImage1.FileName.Length > 0)
                    {
                        NameImage1 = Document.GetShortFileName(inpFileImage1.FileName);
                        Image1 = "ContentItem" + "\\" + ID.ToString() + "\\" + NameImage1;
                        FileFullPath = Document.FileDirPhysicalPathContentItem + ID.ToString() + "\\" + NameImage1;
                    }
                }
                else
                {
                    Image1 = "ContentItem" + "\\" + ID.ToString() + "\\" + NameImage1;
                    FileFullPath = Document.FileDirPhysicalPathContentItem + ID.ToString() + "\\" + NameImage1;
                }
                if (inpFileImage2 != null)
                {
                    if (inpFileImage2.FileName.Length > 0)
                    {
                        ShortFileName = Document.GetShortFileName(inpFileImage2.FileName);
                        Image2 = "ContentItem" + "\\" + ID.ToString() + "\\" + ShortFileName;
                        FileFullPath1 = Document.FileDirPhysicalPathContentItem + ID.ToString() + "\\" + ShortFileName;
                    }
                }
                else
                {
                    Image2 = "ContentItem" + "\\" + ID.ToString() + "\\" + ShortFileName;
                    FileFullPath1 = Document.FileDirPhysicalPathContentItem + ID.ToString() + "\\" + ShortFileName;
                }
                // build SQL statement
                strSQL = "Update tbl_contentItem set Name = '" + DBModule.RefineString(Name) + "'," +
                    "Summary='" + DBModule.RefineString(Summary) + "'," +
                    "Detail='" + DBModule.RefineString(Detail) + "'," +
                    "CreatedDate=" + DBModule.RefineDatetime(CreatedDate) + "," +
                    "CreatedByID=" + CreatedByID + "," +
                    "PostedByID=" + PostedByID + "," +
                    "LastUpdatedDate=" + DBModule.RefineDatetime(LastUpdatedDate) + "," +
                    "LastUpdatedByID=" + LastUpdatedByID + "," +
                    "StartDate=" + DBModule.RefineDatetime(StartDate) + "," +
                    "EndDate=" + DBModule.RefineDatetime(EndDate) + "," +
                    "Degree=" + Degree.ToString() + "," +
                    "IsPosted=" + IsPosted.ToString() + "," +
                    "IsVisible=" + IsVisible.ToString() + "," +
                    "IsCompleted=" + IsCompleted.ToString() + "," +
                    "IsNeverExpired=" + IsNeverExpired.ToString() + ", " +
                    "Image1='" + DBModule.RefineString(Image1) + "'," +
                    "Image2='" + DBModule.RefineString(Image2) + "', " +
                    "OwnerID=" + OwnerID.ToString() + ", " +
                    "SourceID=" + SourceID.ToString() + ", " +
                    "SourceName='" + DBModule.RefineString(SourceName) + "' " +
                    "where ID=" + ID.ToString();
            }
            // run SQL statement
            DBModule.ExecuteNonQuery(strSQL, cn, trans);
            try
            {
                if (inpFileImage1 != null)
                {
                    inpFileImage1.SaveAs(FileFullPath);
                }
                if (inpFileImage2 != null)
                {
                    inpFileImage2.SaveAs(FileFullPath1);
                }
            }
            catch { }
            //if (bNew)
            //{
            //    strSQL = "Insert into TG_VisitNumber(ID, NumOfVisits) values(" + ID +
            //        ", 0)";

            //    DBModule.ExecuteNonQuery(strSQL, cn, trans);
            //}
        }
        public virtual void Delete(MySqlConnection cn, MySqlTransaction trans)
        {
            string strSQL = "";
            // build SQL statement
            strSQL = "Delete from tbl_contentItem where ID=" + ID.ToString();
            // run SQL statement
            DBModule.ExecuteNonQuery(strSQL, cn, trans);
            UserObjectsLog.SaveObjectActionLog("ContentItem", "delete", cn, trans);
            //strSQL = "Delete from tbl_contentItemTranslation where ContentItemID=" + ID.ToString();
            //// run SQL statement
            //DBModule.ExecuteNonQuery(strSQL, cn, trans);
        }

        public void Load(long lID, MySqlConnection cn, MySqlTransaction trans)
        {
            // Error retError = null;
            string strSQL = "";
            try
            {
                //long lDefaultLanguageID = -1; // ldresource.Language.GetDefaultLanguage(cn, trans);

                // build SQL statement
                //if ((lLanguageID == -1) || (lLanguageID == lDefaultLanguageID))

                strSQL = "Select * from tbl_contentItem where ID=" + ID.ToString();

                DataSet ds = DBModule.ExecuteQuery(strSQL, cn, trans);

                // fill data into this object
                if (ds.Tables[0].Rows.Count > 0)
                {
                    DataRow dr = ds.Tables[0].Rows[0];
                    if (!dr.IsNull("Name"))
                        Name = dr["Name"].ToString();
                    if (!dr.IsNull("Summary"))
                        Summary = dr["Summary"].ToString();
                    if (!dr.IsNull("Detail"))
                        Detail = dr["Detail"].ToString();
                    if (!dr.IsNull("CreatedDate"))
                        CreatedDate = DateTime.Parse(dr["CreatedDate"].ToString());
                    if (!dr.IsNull("CreatedByID"))
                        CreatedByID = long.Parse(dr["CreatedByID"].ToString());
                    if (!dr.IsNull("LastUpdatedDate"))
                        CreatedDate = DateTime.Parse(dr["LastUpdatedDate"].ToString());
                    if (!dr.IsNull("LastUpdatedByID"))
                        LastUpdatedByID = long.Parse(dr["LastUpdatedByID"].ToString());
                    if (!dr.IsNull("StartDate"))
                        StartDate = DateTime.Parse(dr["StartDate"].ToString());
                    if (!dr.IsNull("EndDate"))
                        EndDate = DateTime.Parse(dr["EndDate"].ToString());
                    if (!dr.IsNull("Degree"))
                        Degree = int.Parse(dr["Degree"].ToString());
                    if (!dr.IsNull("IsPosted"))
                        IsPosted = int.Parse(dr["IsPosted"].ToString());

                    if (!dr.IsNull("PostedDate"))
                        PostedDate = DateTime.Parse(dr["PostedDate"].ToString());
                    if (!dr.IsNull("PostedByID"))
                        PostedByID = long.Parse(dr["PostedByID"].ToString());

                    if (!dr.IsNull("IsVisible"))
                        IsVisible = int.Parse(dr["IsVisible"].ToString());
                    if (!dr.IsNull("IsCompleted"))
                        IsCompleted = int.Parse(dr["IsCompleted"].ToString());
                    if (!dr.IsNull("IsNeverExpired"))
                        IsNeverExpired = int.Parse(dr["IsNeverExpired"].ToString());

                    if (!dr.IsNull("Image1"))
                        Image1 = dr["Image1"].ToString();
                    if (!dr.IsNull("Image2"))
                        Image2 = dr["Image2"].ToString();
                    if (!dr.IsNull("OwnerID"))
                        OwnerID = long.Parse(dr["OwnerID"].ToString());
                    if (!dr.IsNull("SourceID"))
                        SourceID = long.Parse(dr["SourceID"].ToString());
                    if (!dr.IsNull("SourceName"))
                        SourceName = dr["SourceName"].ToString();
                }
                //else
                //{
                //    retError = new Error();
                //    retError.ID = StringResources.NO_OBJECT_FOUND;
                //    //retError.Description = StringResources.GetDescription(retError.ID, Languages.EN);
                //}
            }
            catch (Exception except)
            {
                // retError = new Error(except);
            }
            //return retError;
        }
        public static void GetList(string strItemIDs, long lCategoryID, long strID, string OrderBy, int NumOfItemsPerPage, int ItemPage, out int NumberOfPages, out DataSet ds, MySqlConnection cn, MySqlTransaction trans)
        {
            int nStatus = ContentItem.All;
            ContentItem.GetList(-1, strItemIDs, strID, "", -1, "", lCategoryID, nStatus, "",
                "Name", NumOfItemsPerPage, ItemPage, out NumberOfPages, out ds, cn, trans);
        }
        public static void GetList(long lLanguageID, string strItemIDs, long strID, string crtDate, long lUserID, string PermissionIDs, long CategoryID,
            int ContentItemStatus, string OtherConditions, string OrderBy, int NumOfItemsPerPage, int ItemPage, out int NumOfPages, out DataSet ds, MySqlConnection cn, MySqlTransaction trans)
        {
            string strSQL = "";
            ds = null;
            NumOfPages = 0;
            // build SQL statement
            long lDefaultLanguageID = -1; // ldresource.Language.GetDefaultLanguage(cn, trans);

            // build SQL statement

            if (CategoryID > 0)
                strSQL = "Select tbl_contentItem.* from tbl_contentItem, tbl_contentItemCategory " +
                    "where tbl_contentItemCategory.ContentCategoryID=" + CategoryID.ToString() + " and " +
                    "tbl_contentItemCategory.ContentItemID = tbl_contentItem.ID";
            else
                strSQL = "Select tbl_contentItem.* from tbl_contentItem";

            #region Status filter
            switch (ContentItemStatus)
            {
                case ContentItem.New:
                    if (strSQL.IndexOf(" where ") < 0)
                        strSQL += " where ";
                    else
                        strSQL += " and ";

                    strSQL = strSQL + "tbl_contentItem.IsCompleted=0";
                    break;
                case ContentItem.Completed:
                    if (strSQL.IndexOf(" where ") < 0)
                        strSQL += " where ";
                    else
                        strSQL += " and ";
                    strSQL = strSQL + "tbl_contentItem.IsCompleted=1";
                    break;
                case ContentItem.UnPosted:
                    if (strSQL.IndexOf(" where ") < 0)
                        strSQL += " where ";
                    else
                        strSQL += " and ";
                    strSQL = strSQL + "tbl_contentItem.IsCompleted=1";
                    strSQL = strSQL + " and tbl_contentItem.IsPosted=0";
                    break;
                case ContentItem.Posted:
                    if (strSQL.IndexOf(" where ") < 0)
                        strSQL += " where ";
                    else
                        strSQL += " and ";
                    strSQL = strSQL + "tbl_contentItem.IsCompleted=1";
                    strSQL = strSQL + " and tbl_contentItem.IsPosted=1";
                    break;
                case ContentItem.Pending:
                    if (strSQL.IndexOf(" where ") < 0)
                        strSQL += " where ";
                    else
                        strSQL += " and ";
                    strSQL = strSQL + "tbl_contentItem.IsCompleted=1";
                    strSQL = strSQL + " and tbl_contentItem.IsPosted=1";
                    strSQL = strSQL + " and tbl_contentItem.StartDate > " + DBModule.RefineDatetime(DateTime.Now);
                    break;
                case ContentItem.Active:
                    if (strSQL.IndexOf(" where ") < 0)
                        strSQL += " where ";
                    else
                        strSQL += " and ";

                    strSQL = strSQL + "tbl_contentItem.IsCompleted=1";
                    strSQL = strSQL + " and tbl_contentItem.IsPosted=1";
                    strSQL = strSQL + " and tbl_contentItem.StartDate <= " + DBModule.RefineDatetime(DateTime.Now);
                    strSQL = strSQL + " and ( (tbl_contentItem.EndDate >= " + DBModule.RefineDatetime(DateTime.Now) + ") ";
                    strSQL = strSQL + " or (tbl_contentItem.IsNeverExpired=1) )";

                    break;
                case ContentItem.Expired:
                    if (strSQL.IndexOf(" where ") < 0)
                        strSQL += " where ";
                    else
                        strSQL += " and ";

                    strSQL = strSQL + "tbl_contentItem.IsCompleted=1";
                    strSQL = strSQL + " and tbl_contentItem.IsPosted=1";
                    strSQL = strSQL + " and tbl_contentItem.StartDate <= " + DBModule.RefineDatetime(DateTime.Now);
                    strSQL = strSQL + " and tbl_contentItem.EndDate < " + DBModule.RefineDatetime(DateTime.Now);
                    strSQL = strSQL + " and tbl_contentItem.IsNeverExpired=0";
                    break;
                case ContentItem.InVisible:
                    if (strSQL.IndexOf(" where ") < 0)
                        strSQL += " where ";
                    else
                        strSQL += " and ";

                    strSQL = strSQL + "tbl_contentItem.IsCompleted=1";
                    strSQL = strSQL + " and tbl_contentItem.IsPosted=1";
                    strSQL = strSQL + " and tbl_contentItem.StartDate <= " + DBModule.RefineDatetime(DateTime.Now);
                    strSQL = strSQL + " and ( (tbl_contentItem.EndDate >= " + DBModule.RefineDatetime(DateTime.Now) + ") ";
                    strSQL = strSQL + " or (tbl_contentItem.IsNeverExpired=1) )";
                    strSQL = strSQL + " and tbl_contentItem.IsVisible=0";
                    break;
            }
            #endregion

            if ((OtherConditions != "") && (OtherConditions != null))
            {
                if (strSQL.IndexOf(" where ") < 0)
                    strSQL += " where ";
                else
                    strSQL += " and ";
                strSQL = strSQL + OtherConditions;
            }

            if ((strItemIDs != "") && (strItemIDs != null))
            {
                if (strSQL.IndexOf(" where ") < 0)
                    strSQL += " where ";
                else
                    strSQL += " and ";

                strSQL = strSQL + "tbl_contentItem.ID in (" + strItemIDs + ")";
            }
            if (strID > 0)
            {
                if (strSQL.IndexOf(" where ") < 0)
                    strSQL += " where ";
                else
                    strSQL += " and ";

                strSQL = strSQL + "tbl_contentItem.ID <> " + strID;
            }
            if ((crtDate != "") && (crtDate != null))
            {
                if (strSQL.IndexOf(" where ") < 0)
                    strSQL += " where ";
                else
                    strSQL += " and ";

                strSQL = strSQL + "tbl_contentItem.StartDate < '" + crtDate + "'";
            }
            if ((OrderBy != null) && (OrderBy != ""))
                strSQL = strSQL + " Order By " + DBModule.RefineString(OrderBy);

            // run SQL statement
            if (NumOfItemsPerPage > 0)
                ds = DBModule.ExecuteQuery(strSQL, NumOfItemsPerPage, ItemPage, out NumOfPages, cn, trans);
            else
                ds = DBModule.ExecuteQuery(strSQL, cn, trans);
        }
        public void GetList(string Condition, string OrderBy, int NumOfItemsPerPage, int PageNo, out int NumberOfPages, out DataSet ds, MySqlConnection cn, MySqlTransaction trans)
        {
            NumberOfPages = 0;
            string strSQL = "";
            ds = null;
            // build SQL statement
            strSQL = "Select tbl_contentItem.*,userCreate.UserName as CreateName,userPosted.UserName as PostedName from tbl_contentItem " +
                " LEFT JOIN tbl_adminusers userCreate ON userCreate.ID=tbl_contentItem.CreatedByID " +
                " LEFT JOIN tbl_adminusers userPosted ON userPosted.ID=tbl_contentItem.PostedByID " +
            "where 1=1 ";
            if (Condition.Length > 0)
            {
                strSQL += Condition;
            }
            if ((OrderBy != null) && (OrderBy != ""))
                strSQL = strSQL + " Order By " + DBModule.RefineString(OrderBy);
            //GV.Response.Write("sql=" + strSQL);
            // run SQL statement
            if (NumOfItemsPerPage > 0)
            {
                ds = DBModule.ExecuteQuery(strSQL, NumOfItemsPerPage, PageNo, out NumberOfPages, cn, trans);
            }
            else
                ds = DBModule.ExecuteQuery(strSQL, cn, trans);

        }
        public static int GetCount(string strItemIDs, long lUserID, string PermissionIDs, long CategoryID, int ContentItemStatus, string OtherConditions, MySqlConnection cn, MySqlTransaction trans)
        {
            int retVal = 0;
            string strSQL = "";
            // build SQL statement
            if (CategoryID > 0)
                strSQL = "Select Count(*) from tbl_contentItem, tbl_contentItemCategory " +
                    "where tbl_contentItemCategory.ContentCategoryID=" + CategoryID.ToString() + " and " +
                    "tbl_contentItemCategory.ContentItemID = tbl_contentItem.ID";
            else
                strSQL = "Select Count(*) from tbl_contentItem";

            #region Status filter
            switch (ContentItemStatus)
            {
                case ContentItem.New:
                    if (strSQL.IndexOf(" where ") < 0)
                        strSQL += " where ";
                    else
                        strSQL += " and ";

                    strSQL = strSQL + "tbl_contentItem.IsCompleted=0";
                    break;
                case ContentItem.Completed:
                    if (strSQL.IndexOf(" where ") < 0)
                        strSQL += " where ";
                    else
                        strSQL += " and ";
                    strSQL = strSQL + "tbl_contentItem.IsCompleted=1";
                    break;
                case ContentItem.UnPosted:
                    if (strSQL.IndexOf(" where ") < 0)
                        strSQL += " where ";
                    else
                        strSQL += " and ";
                    strSQL = strSQL + "tbl_contentItem.IsCompleted=1";
                    strSQL = strSQL + " and tbl_contentItem.IsPosted=0";
                    break;
                case ContentItem.Posted:
                    if (strSQL.IndexOf(" where ") < 0)
                        strSQL += " where ";
                    else
                        strSQL += " and ";
                    strSQL = strSQL + "tbl_contentItem.IsCompleted=1";
                    strSQL = strSQL + " and tbl_contentItem.IsPosted=1";
                    break;
                case ContentItem.Pending:
                    if (strSQL.IndexOf(" where ") < 0)
                        strSQL += " where ";
                    else
                        strSQL += " and ";
                    strSQL = strSQL + "tbl_contentItem.IsCompleted=1";
                    strSQL = strSQL + " and tbl_contentItem.IsPosted=1";
                    strSQL = strSQL + " and tbl_contentItem.StartDate > " + DBModule.RefineDatetime(DateTime.Now);
                    break;
                case ContentItem.Active:
                    if (strSQL.IndexOf(" where ") < 0)
                        strSQL += " where ";
                    else
                        strSQL += " and ";

                    strSQL = strSQL + "tbl_contentItem.IsCompleted=1";
                    strSQL = strSQL + " and tbl_contentItem.IsPosted=1";
                    strSQL = strSQL + " and tbl_contentItem.StartDate <= " + DBModule.RefineDatetime(DateTime.Now);
                    strSQL = strSQL + " and ( (tbl_contentItem.EndDate >= " + DBModule.RefineDatetime(DateTime.Now) + ") ";
                    strSQL = strSQL + " or (tbl_contentItem.IsNeverExpired=1) )";

                    break;
                case ContentItem.Expired:
                    if (strSQL.IndexOf(" where ") < 0)
                        strSQL += " where ";
                    else
                        strSQL += " and ";

                    strSQL = strSQL + "tbl_contentItem.IsCompleted=1";
                    strSQL = strSQL + " and tbl_contentItem.IsPosted=1";
                    strSQL = strSQL + " and tbl_contentItem.StartDate <= " + DBModule.RefineDatetime(DateTime.Now);
                    strSQL = strSQL + " and tbl_contentItem.EndDate < " + DBModule.RefineDatetime(DateTime.Now);
                    strSQL = strSQL + " and tbl_contentItem.IsNeverExpired=0";
                    break;
                case ContentItem.InVisible:
                    if (strSQL.IndexOf(" where ") < 0)
                        strSQL += " where ";
                    else
                        strSQL += " and ";

                    strSQL = strSQL + "tbl_contentItem.IsCompleted=1";
                    strSQL = strSQL + " and tbl_contentItem.IsPosted=1";
                    strSQL = strSQL + " and tbl_contentItem.StartDate <= " + DBModule.RefineDatetime(DateTime.Now);
                    strSQL = strSQL + " and ( (tbl_contentItem.EndDate >= " + DBModule.RefineDatetime(DateTime.Now) + ") ";
                    strSQL = strSQL + " or (tbl_contentItem.IsNeverExpired=1) )";
                    strSQL = strSQL + " and tbl_contentItem.IsVisible=0";
                    break;
            }
            #endregion

            if ((OtherConditions != "") && (OtherConditions != null))
            {
                if (strSQL.IndexOf(" where ") < 0)
                    strSQL += " where ";
                else
                    strSQL += " and ";
                strSQL = strSQL + OtherConditions;
            }

            if ((strItemIDs != "") && (strItemIDs != null))
            {
                if (strSQL.IndexOf(" where ") < 0)
                    strSQL += " where ";
                else
                    strSQL += " and ";

                strSQL = strSQL + "tbl_contentItem.ID in (" + strItemIDs + ")";
            }

            retVal = int.Parse(DBModule.ExecuteQueryForOneResult(strSQL, cn, trans));

            return retVal;
        }
        public static void GetNumOfItemPages(long lUserID, string PermissionIDs, long CategoryID, int ContentItemStatus, string OtherConditions, int NumOfItemsPerPage, out int NumOfPages, MySqlConnection cn, MySqlTransaction trans)
        {
            string strSQLMemberPermission = "(SELECT DISTINCT ResourceID from TS_MemberPermission WHERE UserID=" + lUserID +
                " and PermissionID in (" + PermissionIDs + ") " +
                " UNION SELECT DISTINCT ResourceID from TS_GroupPermission, TS_GroupMember " +
                "WHERE TS_GroupUser.UserID=" + lUserID + " and TS_GroupUser.GroupID = TS_GroupPermission.GroupID " +
                " and TS_GroupPermission.PermissionID in (" + PermissionIDs + ") )";

            string strSQL = "";
            DataSet ds = null;
            NumOfPages = -1;
            // build SQL statement
            if (CategoryID > 0)
                strSQL = "Select (count(*) +" + NumOfItemsPerPage.ToString() + "-1)/" + NumOfItemsPerPage.ToString() +
                    " from tbl_contentItem, tbl_contentItemCategory " +
                    "where tbl_contentItemCategory.CategoryID=" + CategoryID + " and " +
                    "tbl_contentItemCategory.ItemID = tbl_contentItem.ID";
            else
                strSQL = "Select (count(*) + " + NumOfItemsPerPage.ToString() + "-1)/" + NumOfItemsPerPage.ToString() + " from tbl_contentItem";

            #region Status filter
            switch (ContentItemStatus)
            {
                case ContentItem.New:
                    if (strSQL.IndexOf(" where ") < 0)
                        strSQL += " where ";
                    else
                        strSQL += " and ";

                    strSQL = strSQL + "tbl_contentItem.IsCompleted=0";
                    break;
                case ContentItem.Completed:
                    if (strSQL.IndexOf(" where ") < 0)
                        strSQL += " where ";
                    else
                        strSQL += " and ";
                    strSQL = strSQL + "tbl_contentItem.IsCompleted=1";
                    break;
                case ContentItem.UnPosted:
                    if (strSQL.IndexOf(" where ") < 0)
                        strSQL += " where ";
                    else
                        strSQL += " and ";
                    strSQL = strSQL + "tbl_contentItem.IsCompleted=1";
                    strSQL = strSQL + " and tbl_contentItem.IsPosted=0";
                    break;
                case ContentItem.Posted:
                    if (strSQL.IndexOf(" where ") < 0)
                        strSQL += " where ";
                    else
                        strSQL += " and ";
                    strSQL = strSQL + "tbl_contentItem.IsCompleted=1";
                    strSQL = strSQL + " and tbl_contentItem.IsPosted=1";
                    break;
                case ContentItem.Pending:
                    if (strSQL.IndexOf(" where ") < 0)
                        strSQL += " where ";
                    else
                        strSQL += " and ";
                    strSQL = strSQL + "tbl_contentItem.IsCompleted=1";
                    strSQL = strSQL + " and tbl_contentItem.IsPosted=1";
                    strSQL = strSQL + " and tbl_contentItem.StartDate > " + DBModule.RefineDatetime(DateTime.Now);
                    break;
                case ContentItem.Active:
                    if (strSQL.IndexOf(" where ") < 0)
                        strSQL += " where ";
                    else
                        strSQL += " and ";

                    strSQL = strSQL + "tbl_contentItem.IsCompleted=1";
                    strSQL = strSQL + " and tbl_contentItem.IsPosted=1";
                    strSQL = strSQL + " and tbl_contentItem.StartDate <= " + DBModule.RefineDatetime(DateTime.Now);
                    strSQL = strSQL + " and ( (tbl_contentItem.EndDate >= " + DBModule.RefineDatetime(DateTime.Now) + ") ";
                    strSQL = strSQL + " or (tbl_contentItem.IsNeverExpired=1) )";

                    break;
                case ContentItem.Expired:
                    if (strSQL.IndexOf(" where ") < 0)
                        strSQL += " where ";
                    else
                        strSQL += " and ";

                    strSQL = strSQL + "tbl_contentItem.IsCompleted=1";
                    strSQL = strSQL + " and tbl_contentItem.IsPosted=1";
                    strSQL = strSQL + " and tbl_contentItem.StartDate <= " + DBModule.RefineDatetime(DateTime.Now);
                    strSQL = strSQL + " and tbl_contentItem.EndDate < " + DBModule.RefineDatetime(DateTime.Now);
                    strSQL = strSQL + " and tbl_contentItem.IsNeverExpired=0";
                    break;
                case ContentItem.InVisible:
                    if (strSQL.IndexOf(" where ") < 0)
                        strSQL += " where ";
                    else
                        strSQL += " and ";

                    strSQL = strSQL + "tbl_contentItem.IsCompleted=1";
                    strSQL = strSQL + " and tbl_contentItem.IsPosted=1";
                    strSQL = strSQL + " and tbl_contentItem.StartDate <= " + DBModule.RefineDatetime(DateTime.Now);
                    strSQL = strSQL + " and ( (tbl_contentItem.EndDate >= " + DBModule.RefineDatetime(DateTime.Now) + ") ";
                    strSQL = strSQL + " or (tbl_contentItem.IsNeverExpired=1) )";
                    strSQL = strSQL + " and tbl_contentItem.IsVisible=0";
                    break;
            }
            #endregion

            if ((OtherConditions != "") && (OtherConditions != null))
            {
                if (strSQL.IndexOf(" where ") < 0)
                    strSQL += " where ";
                else
                    strSQL += " and ";
                strSQL = strSQL + OtherConditions;
            }

            if (lUserID > 0)
            {
                if (strSQL.IndexOf(" where ") < 0)
                    strSQL += " where ";
                else
                    strSQL += " and ";

                strSQL = strSQL + "tbl_contentItem.ID in " + strSQLMemberPermission;
            }


            // run SQL statement

            ds = DBModule.ExecuteQuery(strSQL, cn, trans);
            NumOfPages = (int)double.Parse(ds.Tables[0].Rows[0][0].ToString());
        }

        public static void PostItem(long ContentItemID, DateTime PostedDate, long PostedByID, MySqlConnection cn, MySqlTransaction trans)
        {
            if (!CheckPosted(ContentItemID, cn, trans))
            {
                string strSQL = "Update tbl_contentItem set IsPosted=1, PostedDate=" + DBModule.RefineDatetime(PostedDate) +
                        ",PostedByID=" + PostedByID +
                        " where ID=" + ContentItemID;

                // run SQL statement
                DBModule.ExecuteNonQuery(strSQL, cn, trans);
            }
        }
        public static void UnPostItem(string ItemIDs, MySqlConnection cn, MySqlTransaction trans)
        {
            string strSQL = "Update tbl_contentItem set IsPosted=0, PostedDate=null, PostedByID=-1" +
            " where ID not in (" + ItemIDs + ")";
            DBModule.ExecuteNonQuery(strSQL, cn, trans);
        }
        public static bool CheckPosted(long lItem, MySqlConnection cn, MySqlTransaction trans)
        {
            bool retVal = false;
            string strSQL = "";
            strSQL = "select top 1 IsPosted from tbl_contentItem" +
                " where ID = " + lItem;
            DataSet ds = DBModule.ExecuteQuery(strSQL, cn, trans);
            if (ds.Tables[0].Rows.Count > 0)
            {
                if (ds.Tables[0].Rows[0][0].ToString() == "1") retVal = true;
            }
            else
                retVal = false;
            return retVal;
        }
        public static void MakeInvisible(long ContentItemID, MySqlConnection cn, MySqlTransaction trans)
        {
            string strSQL = "Update tbl_contentItem set IsVisible=0 where ID=" + ContentItemID;

            // run SQL statement
            DBModule.ExecuteNonQuery(strSQL, cn, trans);
        }
        public static void MakeVisible(long ContentItemID, MySqlConnection cn, MySqlTransaction trans)
        {
            string strSQL = "Update tbl_contentItem set IsVisible=1 where ID=" + ContentItemID;

            // run SQL statement
            DBModule.ExecuteNonQuery(strSQL, cn, trans);
        }
        public static bool CheckVisible(long lItem, MySqlConnection cn, MySqlTransaction trans)
        {
            bool retVal = false;
            string strSQL = "";
            strSQL = "select top 1 IsVisible from tbl_contentItem" +
                " where ID = " + lItem;
            DataSet ds = DBModule.ExecuteQuery(strSQL, cn, trans);
            if (ds.Tables[0].Rows.Count > 0)
            {
                if (ds.Tables[0].Rows[0][0].ToString() == "1") retVal = true;
            }
            else
                retVal = false;
            return retVal;
        }
        public static int GetNumOfVisits(long ContentItemID, DateTime FromDate, DateTime ToDate, MySqlConnection cn, MySqlTransaction trans)
        {
            string strSQL = "";

            // build SQL statement
            strSQL = "select SUM(NumOfVisits) from TG_VisitNumber " +
                " where ID=" + ContentItemID.ToString();

            //if (FromDate != DateTime.Parse(lddata.StringResources.DATE_NULL))
            //{
            //    strSQL += " and VisitDate >=" + DBModule.RefineDatetime(FromDate);
            //}
            //if (ToDate != DateTime.Parse(lddata.StringResources.DATE_NULL))
            //{
            //    strSQL += " and VisitDate <=" + DBModule.RefineDatetime(ToDate);
            //}

            // run SQL statement
            try
            {
                return int.Parse(DBModule.ExecuteQueryForOneResult(strSQL, cn, trans));
            }
            catch
            {
                return 0;
            }
        }
        public static void GetItemVisitsList(long lUserID, string PermissionIDs, long CategoryID, DateTime FromDate, DateTime ToDate, string OrderBy,
            int NumOfItemsPerPage, int ItemPage, out int NumOfPages, out DataSet ds, MySqlConnection cn, MySqlTransaction trans)
        {
            string strSQLMemberPermission = "(SELECT DISTINCT ResourceID from TS_MemberPermission WHERE UserID=" + lUserID +
                "' and PermissionID in (" + PermissionIDs + ") " +
                " UNION SELECT DISTINCT ResourceID from TS_GroupPermission, TS_GroupMember " +
                "WHERE TS_GroupUser.UserID=" + lUserID + " and TS_GroupUser.GroupID = TS_GroupPermission.GroupID " +
                " and TS_GroupPermission.PermissionID in (" + PermissionIDs + ") )";

            string strSQL = "";
            ds = null;
            NumOfPages = 0;
            // build SQL statement
            if (CategoryID > 0)
                strSQL = "Select TG_VisitNumber.ID, SUM(TG_VisitNumber.NumOfVisits) as NumOfVisits from TG_VisitNumber, tbl_contentItemCategory " +
                    "where tbl_contentItemCategory.ContentCategoryID = " + CategoryID + " and " +
                    "tbl_contentItemCategory.ContentItemID = TG_VisitNumber.ID";
            else
                strSQL = "Select TG_VisitNumber.ID, SUM(TG_VisitNumber.NumOfVisits) as NumOfVisits  " +
                    "from TG_VisitNumber where 1=1";

            //if (FromDate != DateTime.Parse(lddata.StringResources.DATE_NULL))
            //{
            //    strSQL += " and TG_VisitNumber.VisitDate >=" + DBModule.RefineDatetime(FromDate);
            //}
            //if (ToDate != DateTime.Parse(lddata.StringResources.DATE_NULL))
            //{
            //    strSQL += " and TG_VisitNumber.VisitDate <" + DBModule.RefineDatetime(ToDate);
            //}

            strSQL += " group by TG_VisitNumber.ID ";

            strSQL = "Select tbl_contentItem.*,B.NumOfVisits from tbl_contentItem,(" + strSQL + ") as B" +
                    " where B.ID=tbl_contentItem.ID ";

            if (lUserID > 0)
            {
                strSQL = strSQL + " and tbl_contentItem.ID in " + strSQLMemberPermission;
            }

            if ((OrderBy != null) && (OrderBy != ""))
                strSQL = strSQL + " Order By " + DBModule.RefineString(OrderBy);

            // run SQL statement
            if (NumOfItemsPerPage > 0)
                ds = DBModule.ExecuteQuery(strSQL, NumOfItemsPerPage, ItemPage, out NumOfPages, cn, trans);
            else
                ds = DBModule.ExecuteQuery(strSQL, cn, trans);
        }
        public static void GetMostVisitedList(long lUserID, string PermissionIDs, long CategoryID, int NumOfItems, string OrderBy, out DataSet ds, MySqlConnection cn, MySqlTransaction trans)
        {
            string strSQLMemberPermission = "(SELECT DISTINCT ResourceID from TS_MemberPermission WHERE UserID=" + lUserID +
                "' and PermissionID in (" + PermissionIDs + ") " +
                " UNION SELECT DISTINCT ResourceID from TS_GroupPermission, TS_GroupMember " +
                "WHERE TS_GroupUser.UserID=" + lUserID + " and TS_GroupUser.GroupID = TS_GroupPermission.GroupID " +
                " and TS_GroupPermission.PermissionID in (" + PermissionIDs + ") )";

            string strSQL = "";
            ds = null;
            // build SQL statement
            if (CategoryID > 0)
                strSQL = "Select top " + NumOfItems.ToString() + " TG_VisitNumber.ID, SUM(TG_VisitNumber.NumOfVisits) as NumOfVisits from TG_VisitNumber, tbl_contentItemCategory " +
                    "where tbl_contentItemCategory.ContentCategoryID = " + CategoryID + " and " +
                    "tbl_contentItemCategory.ContentItemID = TG_VisitNumber.ID group by TG_VisitNumber.ID order by NumOfVisits DESC";
            else
                strSQL = "Select top " + NumOfItems.ToString() + " TG_VisitNumber.ID, SUM(TG_VisitNumber.NumOfVisits) as NumOfVisits  " +
                    "from TG_VisitNumber group by TG_VisitNumber.ID order by NumOfVisits DESC";

            strSQL = "Select tbl_contentItem.*,B.NumOfVisits from tbl_contentItem,(" + strSQL + ") as B" +
                    " where B.ID=tbl_contentItem.ID ";

            if (lUserID > 0)
            {
                strSQL = strSQL + "and tbl_contentItem.ID in " + strSQLMemberPermission;
            }

            if ((OrderBy != null) && (OrderBy != ""))
                strSQL = strSQL + " Order By " + DBModule.RefineString(OrderBy);
            // run SQL statement
            ds = DBModule.ExecuteQuery(strSQL, cn, trans);
        }
        #endregion

        #region ContentItem-Category function
        public void AddToCategory(long CategoryID, MySqlConnection cn, MySqlTransaction trans)
        {
            AddToCategory(ID, CategoryID, cn, trans);
        }
        public static void AddToCategory(long ItemID, long CategoryID, MySqlConnection cn, MySqlTransaction trans)
        {
            if (!IsItemExisting(CategoryID, ItemID, cn, trans))
            {
                string strSQL = "";
                // build SQL statement
                strSQL = "Insert into tbl_contentItemCategory (ContentItemID, ContentCategoryID) " +
                    "values (" + ItemID + "," + CategoryID + ")";
                // run SQL statement
                DBModule.ExecuteNonQuery(strSQL, cn, trans);
            }
        }
        public void RemoveFromCategory(long CategoryID, MySqlConnection cn, MySqlTransaction trans)
        {
            RemoveFromCategory(ID, CategoryID, cn, trans);
        }
        public static void RemoveFromCategory(long ItemID, long CategoryID, MySqlConnection cn, MySqlTransaction trans)
        {
            string strSQL = "";
            // build SQL statement
            strSQL = "Delete from tbl_contentItemCategory where ContentItemID=" + ItemID + " and ContentCategoryID ="
                                                                                    + CategoryID;
            // run SQL statement
            DBModule.ExecuteNonQuery(strSQL, cn, trans);
        }

        public void RemoveFromAllCategory(MySqlConnection cn, MySqlTransaction trans)
        {
            RemoveFromAllCategory(ID, cn, trans);
        }
        public static void RemoveFromAllCategory(long ItemID, MySqlConnection cn, MySqlTransaction trans)
        {
            string strSQL = "";
            // build SQL statement
            strSQL = "Delete from tbl_contentItemCategory where ContentItemID=" + ItemID;
            // run SQL statement
            DBModule.ExecuteNonQuery(strSQL, cn, trans);
        }

        public static void GetDisplayedCategories(long ContentItemID, out DataSet ds, MySqlConnection cn, MySqlTransaction trans)
        {
            string strSQL = "";
            ds = null;
            // build SQL statement
            strSQL = "Select DISTINCT tbl_category.* from tbl_contentItemCategory, tbl_category " +
                "where tbl_contentItemCategory.ContentItemID = " + ContentItemID.ToString() + " and " +
                "tbl_contentItemCategory.ContentCategoryID = tbl_category.ID " +
                "Order By CategoryOrder";
            // run SQL statement
            ds = DBModule.ExecuteQuery(strSQL, cn, trans);
        }

        public static void GetAvailableCategories(long lUserID, string PermissionIDs, long ContentItemID, out DataSet ds, MySqlConnection cn, MySqlTransaction trans)
        {
            string strSQLMemberPermission = "(SELECT DISTINCT ResourceID from TS_MemberPermission WHERE UserID=" + lUserID +
                "' and PermissionID in (" + PermissionIDs + ") " +
                " UNION SELECT DISTINCT ResourceID from TS_GroupPermission, TS_GroupMember " +
                "WHERE TS_GroupUser.UserID=" + lUserID + " and TS_GroupUser.GroupID = TS_GroupPermission.GroupID " +
                " and TS_GroupPermission.PermissionID in (" + PermissionIDs + ") )";

            string strSQL = "";
            ds = null;
            // build SQL statement
            if (ContentItemID > 0)
            {
                strSQL = "Select DISTINCT tbl_category.* from tbl_category where tbl_category.ID NOT IN " +
                    "(Select DISTINCT tbl_category.ID from tbl_contentItemCategory, tbl_category " +
                    "where tbl_contentItemCategory.ContentItemID = " + ContentItemID.ToString() + " and " +
                    "tbl_contentItemCategory.ContentCategoryID = tbl_category.ID)";// +
                //" and tbl_category.IsDisplayedInCategoryList=" + YesNoConstants.Yes.ToString("d");
            }
            else
            {
                strSQL = "Select DISTINCT tbl_category.* from tbl_category";// +
                //  " where tbl_category.IsDisplayedInCategoryList=" + YesNoConstants.Yes.ToString("d");
            }

            if (lUserID > 0)
            {
                if (strSQL.IndexOf(" where ") < 0)
                    strSQL += " where ";
                else
                    strSQL += " and ";

                strSQL = strSQL + "tbl_category.ID in " + strSQLMemberPermission;
            }

            strSQL = strSQL + " Order By Rank";
            // run SQL statement
            ds = DBModule.ExecuteQuery(strSQL, cn, trans);
        }

        public static bool IsItemExisting(long lCategoryID, long lItem, MySqlConnection cn, MySqlTransaction trans)
        {
            bool bIsExisting = false;
            string strSQL = "";
            strSQL = "select top 1 * from tbl_contentItemCategory" +
                " where ContentCategoryID = " + lCategoryID + " and ContentItemID = " + lItem;
            DataSet ds = DBModule.ExecuteQuery(strSQL, cn, trans);
            if (ds.Tables[0].Rows.Count > 0)
                bIsExisting = true;
            else
                bIsExisting = false;
            return bIsExisting;
        }
        #endregion

        #region Related ContentItems functions
        public void GetRelatedItemList(int ContentItemStatus, int NumOfItemsPerPage, int ItemPage, out int NumOfPages, out DataSet ds, MySqlConnection cn, MySqlTransaction trans)
        {
            GetRelatedItemList(ID, ContentItemStatus, NumOfItemsPerPage, ItemPage, out NumOfPages, out ds, cn, trans);
        }
        public static void GetRelatedItemList(long ItemID, int ContentItemStatus, int NumOfItemsPerPage, int ItemPage, out int NumOfPages, out DataSet ds, MySqlConnection cn, MySqlTransaction trans)
        {
            string strSQL = "";
            ds = null;
            NumOfPages = 0;
            // build SQL statement
            strSQL = "Select tbl_contentItem.*, tbl_relatedContentItem.RelatedOrder from tbl_relatedContentItem, tbl_contentItem " +
                " where tbl_relatedContentItem.ContentItemID=" + ItemID + " " +
                    " and tbl_contentItem.ID = tbl_relatedContentItem.RelatedContentItemID " +
                " Order By RelatedOrder";

            #region Status filter
            switch (ContentItemStatus)
            {
                case ContentItem.New:
                    strSQL = strSQL + " and tbl_contentItem.IsCompleted=0";
                    break;
                case ContentItem.Completed:
                    strSQL = strSQL + " and tbl_contentItem.IsCompleted=1";
                    break;
                case ContentItem.UnPosted:
                    strSQL = strSQL + " and tbl_contentItem.IsCompleted=1";
                    strSQL = strSQL + " and tbl_contentItem.IsPosted=0";
                    break;
                case ContentItem.Posted:
                    strSQL = strSQL + " and tbl_contentItem.IsCompleted=1";
                    strSQL = strSQL + " and tbl_contentItem.IsPosted=1";
                    break;
                case ContentItem.Pending:
                    strSQL = strSQL + " and tbl_contentItem.IsCompleted=1";
                    strSQL = strSQL + " and tbl_contentItem.IsPosted=1";
                    strSQL = strSQL + " and tbl_contentItem.StartDate > " + DBModule.RefineDatetime(DateTime.Now);
                    break;
                case ContentItem.Active:
                    strSQL = strSQL + " and tbl_contentItem.IsCompleted=1";
                    strSQL = strSQL + " and tbl_contentItem.IsPosted=1";
                    strSQL = strSQL + " and tbl_contentItem.StartDate <= " + DBModule.RefineDatetime(DateTime.Now);
                    strSQL = strSQL + " and ( (tbl_contentItem.EndDate >= " + DBModule.RefineDatetime(DateTime.Now) + ") ";
                    strSQL = strSQL + " or (tbl_contentItem.IsNeverExpired=1) )";

                    break;
                case ContentItem.Expired:
                    strSQL = strSQL + " and tbl_contentItem.IsCompleted=1";
                    strSQL = strSQL + " and tbl_contentItem.IsPosted=1";
                    strSQL = strSQL + " and tbl_contentItem.StartDate <= " + DBModule.RefineDatetime(DateTime.Now);
                    strSQL = strSQL + " and tbl_contentItem.EndDate < " + DBModule.RefineDatetime(DateTime.Now);
                    strSQL = strSQL + " and tbl_contentItem.IsNeverExpired=0";
                    break;
                case ContentItem.InVisible:
                    strSQL = strSQL + " and tbl_contentItem.IsCompleted=1";
                    strSQL = strSQL + " and tbl_contentItem.IsPosted=1";
                    strSQL = strSQL + " and tbl_contentItem.StartDate <= " + DBModule.RefineDatetime(DateTime.Now);
                    strSQL = strSQL + " and ( (tbl_contentItem.EndDate >= " + DBModule.RefineDatetime(DateTime.Now) + ") ";
                    strSQL = strSQL + " or (tbl_contentItem.IsNeverExpired=1) )";
                    strSQL = strSQL + " and tbl_contentItem.IsVisible=0";
                    break;
            }
            #endregion

            // run SQL statement

            if (NumOfItemsPerPage > 0)
                ds = DBModule.ExecuteQuery(strSQL, NumOfItemsPerPage, ItemPage, out NumOfPages, cn, trans);
            else
                ds = DBModule.ExecuteQuery(strSQL, cn, trans);
        }

        public void AddRelatedItem(long lRelatedItemID, MySqlConnection cn, MySqlTransaction trans)
        {
            AddRelatedItem(ID, lRelatedItemID, cn, trans);
        }
        public static void AddRelatedItem(long lItemID, long lRelatedItemID, MySqlConnection cn, MySqlTransaction trans)
        {
            string strSQL = "";
            // build SQL statement
            strSQL = "Insert into tbl_relatedContentItem(ContentItemID, RelatedContentItemID, RelatedOrder) " +
                " values(" + lItemID + "," + lRelatedItemID + ", 1)";

            // run SQL statement
            DBModule.ExecuteQuery(strSQL, cn, trans);
        }
        public void RemoveRelatedItem(long lRelatedItemID, MySqlConnection cn, MySqlTransaction trans)
        {
            RemoveRelatedItem(ID, lRelatedItemID, cn, trans);
        }
        public static void RemoveRelatedItem(long ItemID, long lRelatedItemID, MySqlConnection cn, MySqlTransaction trans)
        {
            string strSQL = "";
            // build SQL statement
            strSQL = "Delete from tbl_relatedContentItem " +
                " where ContentItemID=" + ItemID + " and RelatedContentItemID=" + lRelatedItemID;

            // run SQL statement
            DBModule.ExecuteQuery(strSQL, cn, trans);
        }
        public void UpdateRelatedItemOrder(long lRelatedItemID, int nNewOrder, MySqlConnection cn, MySqlTransaction trans)
        {
            UpdateRelatedItemOrder(ID, lRelatedItemID, nNewOrder, cn, trans);

        }
        public static void UpdateRelatedItemOrder(long lItemID, long lRelatedItemID, int nNewOrder, MySqlConnection cn, MySqlTransaction trans)
        {
            string strSQL = "";
            // build SQL statement
            strSQL = "Update tbl_relatedContentItem " +
                " set RelatedOrder=" + nNewOrder.ToString() +
                " where ContentItemID=" + lItemID + " and RelatedContentItemID=" + lRelatedItemID;

            // run SQL statement
            DBModule.ExecuteQuery(strSQL, cn, trans);
        }
        public static bool IsRelatedItem(long lItemID, long lRelatedItemID, MySqlConnection cn, MySqlTransaction trans)
        {
            bool retVal = false;
            string strSQL = "";
            strSQL = "select top 1 * from tbl_relatedContentItem" +
                " where ContentItemID = " + lItemID + " and RelatedContentItemID = " + lRelatedItemID;
            DataSet ds = DBModule.ExecuteQuery(strSQL, cn, trans);
            if (ds.Tables[0].Rows.Count > 0)
                retVal = true;
            else
                retVal = false;
            return retVal;
        }
        #endregion

        public static void doSearch(string strKeywords, string strCategoryIDs, string strBeginStartDate, string strEndStartDate, int ContentItemStatus, string OtherConditions, string OrderBy, int NumOfItemsPerPage, int ItemPage, out int NumOfPages, out DataSet ds, MySqlConnection cn, MySqlTransaction trans)
        {
            string strSQL = "";
            ds = null;
            NumOfPages = 0;
            // build SQL statement
            if ((strCategoryIDs != "") && (strCategoryIDs != null))
                strSQL = "Select tbl_contentItem.ID from tbl_contentItem, tbl_contentItemCategory " +
                    "where tbl_contentItemCategory.Name like '%" + strCategoryIDs + "%' and " +
                    "tbl_contentItemCategory.ContentItemID = tbl_contentItem.ID";
            else
                strSQL = "Select tbl_contentItem.ID from tbl_contentItem";

            if ((strKeywords != "") && (strKeywords != null))
            {
                if (strSQL.IndexOf(" where ") < 0)
                    strSQL += " where (";
                else
                    strSQL += " and (";
                strSQL = strSQL + " Name like '%" + strKeywords + "%')";
                //strSQL = strSQL + " or Summary like '%" + strKeywords + "%'";
                //strSQL = strSQL + " or Detail like '%" + strKeywords + "%')";
            }

            #region Status filter
            switch (ContentItemStatus)
            {
                case ContentItem.New:
                    if (strSQL.IndexOf(" where ") < 0)
                        strSQL += " where ";
                    else
                        strSQL += " and ";

                    strSQL = strSQL + "tbl_contentItem.IsCompleted=0";
                    break;
                case ContentItem.Completed:
                    if (strSQL.IndexOf(" where ") < 0)
                        strSQL += " where ";
                    else
                        strSQL += " and ";
                    strSQL = strSQL + "tbl_contentItem.IsCompleted=1";
                    break;
                case ContentItem.UnPosted:
                    if (strSQL.IndexOf(" where ") < 0)
                        strSQL += " where ";
                    else
                        strSQL += " and ";
                    strSQL = strSQL + "tbl_contentItem.IsCompleted=1";
                    strSQL = strSQL + " and tbl_contentItem.IsPosted=0";
                    break;
                case ContentItem.Posted:
                    if (strSQL.IndexOf(" where ") < 0)
                        strSQL += " where ";
                    else
                        strSQL += " and ";
                    strSQL = strSQL + "tbl_contentItem.IsCompleted=1";
                    strSQL = strSQL + " and tbl_contentItem.IsPosted=1";
                    break;
                case ContentItem.Pending:
                    if (strSQL.IndexOf(" where ") < 0)
                        strSQL += " where ";
                    else
                        strSQL += " and ";
                    strSQL = strSQL + "tbl_contentItem.IsCompleted=1";
                    strSQL = strSQL + " and tbl_contentItem.IsPosted=1";
                    strSQL = strSQL + " and tbl_contentItem.StartDate > " + DBModule.RefineDatetime(DateTime.Now);
                    break;
                case ContentItem.Active:
                    if (strSQL.IndexOf(" where ") < 0)
                        strSQL += " where ";
                    else
                        strSQL += " and ";

                    strSQL = strSQL + "tbl_contentItem.IsCompleted=1";
                    strSQL = strSQL + " and tbl_contentItem.IsPosted=1";
                    strSQL = strSQL + " and tbl_contentItem.StartDate <= " + DBModule.RefineDatetime(DateTime.Now);
                    strSQL = strSQL + " and ( (tbl_contentItem.EndDate >= " + DBModule.RefineDatetime(DateTime.Now) + ") ";
                    strSQL = strSQL + " or (tbl_contentItem.IsNeverExpired=1) )";

                    break;
                case ContentItem.Expired:
                    if (strSQL.IndexOf(" where ") < 0)
                        strSQL += " where ";
                    else
                        strSQL += " and ";

                    strSQL = strSQL + "tbl_contentItem.IsCompleted=1";
                    strSQL = strSQL + " and tbl_contentItem.IsPosted=1";
                    strSQL = strSQL + " and tbl_contentItem.StartDate <= " + DBModule.RefineDatetime(DateTime.Now);
                    strSQL = strSQL + " and tbl_contentItem.EndDate < " + DBModule.RefineDatetime(DateTime.Now);
                    strSQL = strSQL + " and tbl_contentItem.IsNeverExpired=0";
                    break;
                case ContentItem.InVisible:
                    if (strSQL.IndexOf(" where ") < 0)
                        strSQL += " where ";
                    else
                        strSQL += " and ";

                    strSQL = strSQL + "tbl_contentItem.IsCompleted=1";
                    strSQL = strSQL + " and tbl_contentItem.IsPosted=1";
                    strSQL = strSQL + " and tbl_contentItem.StartDate <= " + DBModule.RefineDatetime(DateTime.Now);
                    strSQL = strSQL + " and ( (tbl_contentItem.EndDate >= " + DBModule.RefineDatetime(DateTime.Now) + ") ";
                    strSQL = strSQL + " or (tbl_contentItem.IsNeverExpired=1) )";
                    strSQL = strSQL + " and tbl_contentItem.IsVisible=0";
                    break;
            }
            #endregion

            if ((OtherConditions != "") && (OtherConditions != null))
            {
                if (strSQL.IndexOf(" where ") < 0)
                    strSQL += " where ";
                else
                    strSQL += " and ";
                strSQL = strSQL + OtherConditions;
            }

            if ((strBeginStartDate != "") && (strBeginStartDate != null))
            {
                if (strSQL.IndexOf(" where ") < 0)
                    strSQL += " where ";
                else
                    strSQL += " and ";
                strSQL = strSQL + " StartDate >= " + DBModule.RefineDatetime(strBeginStartDate);
            }

            if ((strEndStartDate != "") && (strEndStartDate != null))
            {
                if (strSQL.IndexOf(" where ") < 0)
                    strSQL += " where ";
                else
                    strSQL += " and ";
                strSQL = strSQL + " StartDate <= " + DBModule.RefineDatetime(strEndStartDate);
            }

            if ((OrderBy != null) && (OrderBy != ""))
                strSQL = strSQL + " Order By " + DBModule.RefineString(OrderBy);
            // run SQL statement
            if (NumOfItemsPerPage > 0)
                ds = DBModule.ExecuteQuery(strSQL, NumOfItemsPerPage, ItemPage, out NumOfPages, cn, trans);
            else
                ds = DBModule.ExecuteQuery(strSQL, cn, trans);
        }
    }
}
